<?php

/* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */

/**
 * This file contains the PDODB_Connection_mysql class.
 *
 * PHP version 5
 *
 * This file is part of PEAR::PDODB - A database abstraction layer for PHP5 built
 * using PDO. PHP version 5.1+ is recommended.
 *
 * PEAR::PDODB is free software: you can redistribute it and/or modify it under
 * the terms of the GNU Lesser General Public License as published by the Free
 * Software Foundation, either version 3 of the License, or (at your option)
 * any later version.
 *
 * PEAR::PDODB is distributed in the hope that it will be useful, but WITHOUT ANY
 * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
 * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
 * details.
 *
 * You should have received a copy of the GNU Lesser General Public License
 * along with PEAR::PDODB. If not, see <http://www.gnu.org/licenses/>.
 *
 * @package     PDODB
 * @subpackage  mysql
 * @category    Database
 * @author      Lukas Smith <smith@pooteeweet.org>
 * @author      Michael J. I. Jackson <mjijackson@gmail.com>
 * @copyright   1998-2007 Manuel Lemos, Tomas V.V. Cox, Stig S. Bakken, Lukas Smith
 * @copyright   2007 Michael J. I. Jackson
 * @license     http://www.gnu.org/licenses/lgpl-3.0.txt GNU LGPL 3.0
 * @version     SVN: $Id: mysql.php 14 2007-12-10 22:58:02Z mjijackson $
 * @link        http://pear.php.net/packages/PDODB
 */

/**
 * Require PDODB_Connection
 */
require_once dirname(__FILE__) . '.php';

/**
 * A MySQL-specific database connection.
 *
 * @package     PDODB
 * @category    Database
 * @author      Lukas Smith <smith@pooteeweet.org>
 * @author      Michael J. I. Jackson <mjijackson@gmail.com>
 */
class PDODB_Connection_mysql extends PDODB_Connection
{

    /**
     * @see     PDODB_Connection::$id_quotes
     */
    protected $id_quotes = array(
        'start'     => '`',
        'end'       => '`',
        'escape'    => '`'
    );

    /**
     * @see     PDODB_Connection::init()
     */
    public function init()
    {
        // Note: Sub-queries first supported in MySQL 4.1.0 (see
        // http://dev.mysql.com/doc/refman/4.1/en/news-4-1-0.html)
        $this->supports['sub_queries'] = version_compare($this->getServerVersion(),
            '4.1.0') > -1;
        $this->supports['limit_queries'] = true;

        $this->varchar_max_length = 255;
    }

    /**
     * @see     PDODB_Connection::errorCode()
     */
    public function errorCode(PDOException $e)
    {
        static $e_codes;

        if (empty($e_codes)) {
            $e_codes = array(
                1004 => PDODB::E_CANNOT_CREATE,
                1005 => PDODB::E_CANNOT_CREATE,
                1006 => PDODB::E_CANNOT_CREATE,
                1007 => PDODB::E_ALREADY_EXISTS,
                1008 => PDODB::E_CANNOT_DROP,
                1022 => PDODB::E_ALREADY_EXISTS,
                1044 => PDODB::E_ACCESS_VIOLATION,
                1045 => PDODB::E_ACCESS_VIOLATION,
                1046 => PDODB::E_NODBSELECTED,
                1048 => PDODB::E_CONSTRAINT,
                1049 => PDODB::E_NOSUCHDB,
                1050 => PDODB::E_ALREADY_EXISTS,
                1051 => PDODB::E_NOSUCHTABLE,
                1054 => PDODB::E_NOSUCHCOLUMN,
                1061 => PDODB::E_ALREADY_EXISTS,
                1062 => PDODB::E_ALREADY_EXISTS,
                1064 => PDODB::E_SYNTAX,
                1091 => PDODB::E_NOT_FOUND,
                1100 => PDODB::E_NOT_LOCKED,
                1142 => PDODB::E_ACCESS_VIOLATION,
                1146 => PDODB::E_NOSUCHTABLE,
                1216 => PDODB::E_CONSTRAINT,
                1217 => PDODB::E_CONSTRAINT,
                1356 => PDODB::E_DIVZERO,
                1451 => PDODB::E_CONSTRAINT,
                1452 => PDODB::E_CONSTRAINT,
            );
        }

        $d_code = $e->errorInfo[1];
        // sometimes errorInfo is empty...hack!
        if (is_null($d_code) && preg_match('/ \[(\d+)\] /', $e->getMessage(), $match)) {
            $d_code = $match[1];
        }

        return isset($e_codes[$d_code]) ? $e_codes[$d_code] : PDODB::E_UNKNOWN;
    }

    /**
     * @see     PDODB_Connection::modifyQuery()
     */
    protected function modifyQuery($query, $is_manip, $limit, $offset)
    {
        // "DELETE FROM table" gives 0 affected rows in MySQL.
        // This little hack lets you know how many rows were deleted.
        if (preg_match('/^\s*DELETE\s+FROM\s+(\S+)\s*$/i', $query)) {
            $query = preg_replace('/^\s*DELETE\s+FROM\s+(\S+)\s*$/',
                'DELETE FROM \1 WHERE 1=1', $query);
        }

        if ($limit > 0
            && !preg_match('/LIMIT\s*\d(?:\s*(?:,|OFFSET)\s*\d+)?(?:[^\)]*)?$/i', $query)
        ) {
            $query = rtrim($query);
            if (substr($query, -1) == ';') {
                $query = substr($query, 0, -1);
            }

            // LIMIT doesn't always come last in the query
            // @see http://dev.mysql.com/doc/refman/5.0/en/select.html
            $after = '';
            if (preg_match('/(\s+INTO\s+(?:OUT|DUMP)FILE\s.*)$/ims', $query, $matches)) {
                $after = $matches[0];
                $query = preg_replace('/(\s+INTO\s+(?:OUT|DUMP)FILE\s.*)$/ims', '', $query);
            } elseif (preg_match('/(\s+FOR\s+UPDATE\s*)$/i', $query, $matches)) {
               $after = $matches[0];
               $query = preg_replace('/(\s+FOR\s+UPDATE\s*)$/im', '', $query);
            } elseif (preg_match('/(\s+LOCK\s+IN\s+SHARE\s+MODE\s*)$/im', $query, $matches)) {
               $after = $matches[0];
               $query = preg_replace('/(\s+LOCK\s+IN\s+SHARE\s+MODE\s*)$/im', '', $query);
            }

            if ($is_manip) {
                return $query . " LIMIT $limit" . $after;
            } else {
                return $query . " LIMIT $offset, $limit" . $after;
            }
        }

        return $query;
    }

    /**
     * @see     PDODB_Connection::getDatabase()
     */
    public function getDatabase()
    {
        return $this->queryOne('SELECT DATABASE()');
    }

    /**
     * @see     PDODB_Connection::setDatabase()
     */
    public function setDatabase($name)
    {
        $old_db = $this->getDatabase();
        $this->exec('USE ' . $this->quoteIdentifier($name, true));
        return $old_db;
    }

    /**
     * @see     PDODB_Connection::listDatabases()
     */
    public function listDatabases()
    {
        return $this->queryCol('SHOW DATABASES');
    }

    /**
     * @see     PDODB_Connection::listTables()
     */
    public function listTables()
    {
        return $this->queryCol('SHOW TABLES');
    }

    /**
     * @see     PDODB_Connection::listColumns()
     */
    public function listColumns($table)
    {
        return $this->queryCol('SHOW COLUMNS FROM ' . $this->quoteIdentifier($table, true));
    }

    /**
     * @see     PDODB_Connection::listViews()
     */
    public function listViews()
    {
        return $this->queryCol("SHOW FULL TABLES WHERE Table_type = 'VIEW'");
    }

    /**
     * @see     PDODB_Connection::listIndexes()
     */
    public function listIndexes($table)
    {
        $key_name = 'Key_name';
        $non_unique = 'Non_unique';

        switch ($this->getAttribute(PDO::ATTR_CASE)) {
            case PDO::CASE_LOWER:
                $key_name = strtolower($key_name);
                $non_unique = strtolower($non_unique);
            break;
            case PDO::CASE_UPPER:
                $key_name = strtoupper($key_name);
                $non_unique = strtoupper($non_unique);
            break;
        }

        $query = 'SHOW INDEX FROM ' . $this->quoteIdentifier($table, true);
        $indexes = $this->queryAll($query);

        $result = array();
        foreach ($indexes as $index_data) {
            if ($index_data[$non_unique]
                && ($index = $this->fixIndexName($index_data[$key_name]))) {
                    $result[$index] = true;
            }
        }

        return array_keys($result);
    }

    /**
     * @see     PDODB_Connection::listConstraints()
     */
    public function listConstraints($table)
    {
        $key_name = 'Key_name';
        $non_unique = 'Non_unique';

        switch ($this->getAttribute(PDO::ATTR_CASE)) {
            case PDO::CASE_LOWER:
                $key_name = strtolower($key_name);
                $non_unique = strtolower($non_unique);
            break;
            case PDO::CASE_UPPER:
                $key_name = strtoupper($key_name);
                $non_unique = strtoupper($non_unique);
            break;
        }

        $query = 'SHOW INDEX FROM ' . $this->quoteIdentifier($table, true);
        $indexes = $this->queryAll($query);

        $result = array();
        foreach ($indexes as $index_data) {
            if (!$index_data[$non_unique]) {
                if ($index_data[$key_name] !== 'PRIMARY') {
                    $index = $this->fixIndexName($index_data[$key_name]);
                } else {
                    $index = 'PRIMARY';
                }
                if (!empty($index)) {
                    $result[$index] = true;
                }
            }
        }

        return array_keys($result);
    }

    /**
     * @see     PDODB_Connection::listSequences()
     */
    public function listSequences()
    {
        $sequences = array();
        foreach ($this->listTables() as $table) {
            if ($seq_name = $this->fixSequenceName($table, true)) {
                $sequences[] = $seq_name;
            }
        }

        return $sequences;
    }

    /**
     * @see     PDODB_Connection::listFunctions()
     */
    public function listFunctions()
    {
        return $this->queryCol('SELECT NAME FROM mysql.PROC');
    }

    /**
     * @see     PDODB_Connection::listUsers()
     */
    public function listUsers()
    {
        return $this->queryCol('SELECT DISTINCT USER FROM mysql.USER');
    }

    /**
     * @see     PDODB_Connection::nextId()
     */
    public function nextId($seq_name, $ondemand = true)
    {
        $sequence_name = $this->quoteIdentifier($this->sequenceName($seq_name), true);
        $seqcol_name = $this->quoteIdentifier($this->getAttribute(PDODB::ATTR_SEQCOL_NAME), true);

        try {
            $this->exec("INSERT INTO $sequence_name ($seqcol_name) VALUES (NULL)");
        } catch (PDODB_NoSuchTableException $e) {
            if ($ondemand) {
                try {
                    $this->manager->createSequence($seq_name);
                    return $this->nextID($seq_name, false);
                } catch (PDODB_Exception $e) {
                    throw new PDODB_CannotCreateException(
                        "Failed to create on-demand sequence $seq_name",
                        $e, $this->lastQuery());
                }
            } else {
                throw $e;
            }
        }

        $id = $this->lastInsertId();
        if (is_numeric($id)) {
            try {
                $this->exec("DELETE FROM $sequence_name WHERE $seqcol_name < $id");
            } catch (PDODB_Exception $e) {
                $this->raiseWarning(get_class($this), __FUNCTION__,
                    "Could not delete previous sequence table values from $seq_name");
            }
        }

        return $id;
    }

    /**
     * @see     PDODB_Connection::currId()
     */
    public function currId($seq_name)
    {
        $sequence_name = $this->quoteIdentifier($this->sequenceName($seq_name), true);
        $seqcol_name = $this->quoteIdentifier($this->getAttribute(PDODB::ATTR_SEQCOL_NAME), true);
        return $this->queryOne("SELECT MAX($seqcol_name) FROM $sequence_name");
    }

}

?>
